#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/12/4 13:34
# @Author : 闫峰
# @File    : listing_filter_9058.py
import datetime
import decimal
import json
from decimal import Decimal
import requests
import os
import time
from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
from utils.mysqlUtils.db_pool import Database
from utils.mysqlUtils.mongodb_utils import MongoDBLink

current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])


def remove_file(info_path):
    if os.path.exists(info_path):
        os.remove(info_path)
    else:
        print(f"{info_path} 不存在")


info_path = ensure_path_sep(f"\\logs\\info-{dis_path}{time.time()}")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}{time.time()}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}{time.time()}.log")
remove_file(info_path)
remove_file(error_path)
remove_file(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')


class SkuFilter:
    """
    listing 分级
    """
    db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
    # 定义mongodb
    modb = MongoDBLink(
        uri='mongodb://admin:Zhcx?2021@10.158.158.29:27017/?authSource=admin',
        dbname='test_zhcxkj_java_center', coll_name='t_sku_strategy_relation')

    def __init__(self, template_id):
        # 查询策略
        self.template_id = template_id
        get_stragety = """
        SELECT `status`
        FROM test_zhcxkj_center_strategy_comprehensive.t_walmart_listing_rules_template 
        WHERE id= %s
        """
        template = self.db.fetch_all_tuple(get_stragety, (template_id,))
        if not len(template):
            pass
        if not template[0][0]:
            pass
        # 查询策略条件
        get_strategy_conditions = """
        SELECT listing_type,template_field,template_value
        FROM test_zhcxkj_center_strategy_comprehensive.t_walmart_listing_rules_template_detail 
        WHERE template_id= %s
        """
        strategy_conditions = self.db.fetch_all_tuple(get_strategy_conditions, (template_id,))
        if not len(strategy_conditions):
            raise
        self.strategy_conditions = {}
        for data in strategy_conditions:
            if data[1] not in self.strategy_conditions:
                self.strategy_conditions[data[1]] = json.loads(data[2])

        print(strategy_conditions)

    def sku_filter(self, source_id):
        # 分批次拉取数据源 在线的listing数据
        sql1 = f"""
        SELECT 
        a.id,                   -- 0
        a.source_id,            -- 1
        a.source_sku,           -- 2
        a.first_publish_time,   -- 3
        a.sale_price,           -- 4
        a.sku,                  -- 5
        a.has_variant,          -- 6
        b.availtosell_quantity, -- 7
        a.item_id               -- 8
        FROM test_zhcxkj_center_listing_comprehensive.t_walmart_listing a
        LEFT JOIN test_zhcxkj_center_listing_comprehensive.t_walmart_quantity b ON a.source_id=b.source_id AND a.source_sku=b.source_sku AND a.item_id=b.item_id
        WHERE a.id > %s
        and a.source_id=%s
        and a.status = 1
        and a.is_deleted=0
        order by a.id
        limit 500
        """
        id_index = 0
        data_set_total = set()
        while True:
            INFO.logger.info(f"id_index={id_index}:")
            data_list = self.db.fetch_all_tuple(sql1, (id_index, source_id))
            if not len(data_list):
                break
            id_index = data_list[-1][0]
            data_set = set(data_list)
            # 过滤掉sku为空 的数据
            data_set = set(filter(lambda x:x[5] is not None,data_set))
            if not len(data_set):
                continue
            # 过滤掉 价格为空 的数据
            data_set = set(filter(lambda x:x[4] is not None,data_set))
            if not len(data_set):
                continue
            # 过滤掉可售数为空 的数据
            data_set = set(filter(lambda x: x[7] is not None,data_set))
            if not data_set:
                continue
            # 产品上架到店铺的时间
            if self.strategy_conditions.get("publishTime"):
                origin_data_set = data_set
                timemin = datetime.datetime.strptime(self.strategy_conditions.get("publishTime").get('timeMin'),
                                                     "%Y-%m-%d %H:%M:%S")
                timemax = datetime.datetime.strptime(self.strategy_conditions.get("publishTime").get('timeMax'),
                                                     "%Y-%m-%d %H:%M:%S")
                data_set = set(filter(lambda x: timemin <= x[3] <= timemax, data_set))
                INFO.logger.info(f"分类id条件过滤了{list(map(lambda x: x[0], origin_data_set - data_set))}")
            if not data_set:
                continue
            # Listing售价
            if self.strategy_conditions.get("listingSalePrice"):
                origin_data_set = data_set
                min_price_str = self.strategy_conditions.get("listingSalePrice").get("numMin")
                max_price_str = self.strategy_conditions.get("listingSalePrice").get("numMax")
                # min_price = decimal.Decimal(self.strategy_conditions.get("listingSalePrice").get("numMin"))
                # max_price = decimal.Decimal(self.strategy_conditions.get("listingSalePrice").get("numMax"))
                if all([min_price_str,max_price_str]):
                    data_set = set(filter(lambda x: decimal.Decimal(min_price_str) <= x[4] <= decimal.Decimal(max_price_str), list(data_set)))
                elif all([not min_price_str,max_price_str]):
                    data_set = set(filter(lambda x: x[4] <= decimal.Decimal(max_price_str), list(data_set)))
                elif all([min_price_str,not max_price_str]):
                    data_set = set(filter(lambda x: decimal.Decimal(min_price_str) <= x[4], list(data_set)))
                INFO.logger.info(f"listing售价条件过滤了{list(map(lambda x: x[0], origin_data_set - data_set))}")
            if not data_set:
                continue
            # sku销量
            if self.strategy_conditions.get("totalSaleNum"):
                data_set = self.sku_sales_filter(data_set)
            if not data_set:
                continue
            # 平台listing销量
            if self.strategy_conditions.get("platformSaleNum"):
                data_set = self.platform_sku_sales_filter(data_set)


            if not data_set:
                continue
            # 可售数
            if self.strategy_conditions.get("availableNum"):
                avalitosell_quantity_max = self.strategy_conditions.get('availableNum').get('numMax')
                avalitosell_quantity_min = self.strategy_conditions.get('availableNum').get('numMin')
                if all([avalitosell_quantity_max, avalitosell_quantity_min]):
                    data_set = set(filter(lambda x: Decimal(avalitosell_quantity_min) <= x[7] <= Decimal(avalitosell_quantity_max), list(data_set)))
                elif all([ avalitosell_quantity_max, not avalitosell_quantity_min]):
                    data_set = set(
                        filter(lambda x: x[7] <= Decimal(avalitosell_quantity_max), list(data_set)))
                elif all([not avalitosell_quantity_max, avalitosell_quantity_min]):
                    data_set = set(
                        filter(lambda x: Decimal(avalitosell_quantity_min) <= x[7] , list(data_set)))
                # data_set = self.filter_avalitosell_quantity(data_set, source_id)
            if not data_set:
                continue
            # SKU上架次数
            if self.strategy_conditions.get("publishNum"):
                data_set = set(filter(self.filter_publish_num, list(data_set)))


            data_set_total |= data_set

        if not data_set_total:
            ERROR.logger.error(f"最终过滤结果数为0")

            return
        get_sku_list = f"""
        SELECT source_id,source_sku,item_id
        FROM test_zhcxkj_center_strategy_comprehensive.t_walmart_listing_rules_template_sku
        WHERE template_id=%s
        AND is_deleted=0 
        """
        actucl_list = self.db.fetch_all_tuple(get_sku_list, (self.template_id,))
        actucl_set = set(actucl_list)
        ERROR.logger.error(f"最终过滤结果数为{len(data_set_total)}")
        ERROR.logger.error(f"最终过滤结果为{data_set_total}")
        expect_set = set(map(lambda x: (x[1],x[2],x[8]), data_set_total))
        if expect_set == actucl_set:
            pass
        else:
            print()


    def filter_avalitosell_quantity(self, data_set, source_id):
        source_sku_set = set(map(lambda x: x[2], data_set))
        avalitosell_quantity_max = self.strategy_conditions.get('availableNum').get('numMax')
        avalitosell_quantity_min = self.strategy_conditions.get('availableNum').get('numMin')
        source_sku_list = None
        if all([avalitosell_quantity_max,avalitosell_quantity_min]):
            get_lsource_sku_list = f"""
            SELECT source_sku
            FROM test_zhcxkj_center_listing_comprehensive.t_walmart_quantity 
            WHERE source_id=%s
            AND source_sku in %s
            and avalitosell_quantity >= %s
            and avalitosell_quantity <= %s
            AND is_deleted=0
            """
            source_sku_list = self.db.fetch_all_tuple(get_lsource_sku_list, (
                source_id, source_sku_set, int(avalitosell_quantity_min), int(avalitosell_quantity_max)))
        elif all([not avalitosell_quantity_max,avalitosell_quantity_min]):
            get_lsource_sku_list = f"""
            SELECT source_sku
            FROM test_zhcxkj_center_listing_comprehensive.t_walmart_quantity 
            WHERE source_id=%s
            AND source_sku in %s
            and avalitosell_quantity >= %s
            AND is_deleted=0
            """
            source_sku_list = self.db.fetch_all_tuple(get_lsource_sku_list, (
                source_id, source_sku_set, int(avalitosell_quantity_min)))
        elif all([not avalitosell_quantity_max,avalitosell_quantity_min]):
            get_lsource_sku_list = f"""
            SELECT source_sku
            FROM test_zhcxkj_center_listing_comprehensive.t_walmart_quantity 
            WHERE source_id=%s
            AND source_sku in %s
            and avalitosell_quantity <= %s
            AND is_deleted=0
            """
            source_sku_list = self.db.fetch_all_tuple(get_lsource_sku_list, (
                source_id, source_sku_set, int(avalitosell_quantity_max)))
        if not len(source_sku_list):
            return set()
        source_sku_set_participate_activities = set(map(lambda x: x[0], source_sku_list))
        sql = """
                SELECT id,source_id,source_sku,first_publish_time,sale_price,sku
        FROM `test_zhcxkj_center_listing_comprehensive`.`t_walmart_listing` 
        WHERE source_sku in %s
        and source_id=%s
        and status = 1
        and is_deleted=0
        """
        res = self.db.fetch_all_tuple(sql, (source_sku_set_participate_activities,source_id))
        data_set = set(res)

        # INFO.logger.info(f"参与促销活动过过滤了 {listing_id_set_participate_activities}")
        # get_data_set = f"""
        #         SELECT id,category_id,market_create_time,price,sku
        # FROM `test_zhcxkj_center_listing_comprehensive`.`t_allegro_listing_{num}`
        # WHERE id in %s
        # """
        # data_set = self.db.fetch_all_tuple(get_data_set,(listing_id_set-listing_id_set_participate_activities,))
        # if not len(data_set):
        #     return None
        # return set(data_set)
        return data_set

    def sku_sales_filter(self, data_set):
        oringin_data_set = data_set
        sku_list = list(map(lambda x: x[5], data_set))
        #   根据策略规则获取销量
        if self.strategy_conditions.get("totalSaleNum").get('type') == -1:
            sku_sale_recent_days = None
        else:
            sku_sale_recent_days = self.strategy_conditions.get("totalSaleNum").get('dayNum')

        # 得到sku 销量字典
        sales_dict = self.get_platform_sales(sku_list, "all", sku_sale_recent_days)
        # 匹配策略
        if all([self.strategy_conditions.get("totalSaleNum").get('numMin'),
                not self.strategy_conditions.get("totalSaleNum").get('numMax')]):
            sales_dict = set(filter(lambda x: int(self.strategy_conditions.get("totalSaleNum").get('numMin')) <= x[1],
                                    sales_dict.items()))

        elif all([not self.strategy_conditions.get("totalSaleNum").get('numMin'),
                  self.strategy_conditions.get("totalSaleNum").get('numMax')]):
            sales_dict = set(filter(lambda x: x[1] <= int(self.strategy_conditions.get("totalSaleNum").get('numMax')),
                                    sales_dict.items()))

        elif all([self.strategy_conditions.get("totalSaleNum").get('numMin'),
                  self.strategy_conditions.get("totalSaleNum").get('numMax')]):
            sales_dict = set(filter(
                lambda x: int(self.strategy_conditions.get("totalSaleNum").get('numMin')) <= x[1] <= int(self.strategy_conditions.get("totalSaleNum").get('numMax')),
                sales_dict.items()))

        # 得到符合策略的sku列表
        sku_list = list(map(lambda x: x[0], sales_dict))
        # 得到过滤后的集合
        data_set = set(filter(lambda x: x[5] in sku_list, oringin_data_set))

        INFO.logger.info(f"SKU销量过滤了{set(map(lambda x: x[5], oringin_data_set - data_set))}")

        return data_set

    def platform_sku_sales_filter(self, data_set):

        oringin_data_set = data_set
        sku_list = list(map(lambda x: x[5], data_set))
        #   根据策略规则获取销量
        if self.strategy_conditions.get("platformSaleNum").get('type') == -1:
            sku_sale_recent_days = None
        else:
            sku_sale_recent_days = self.strategy_conditions.get("platformSaleNum").get('dayNum')
        # 得到sku 销量字典
        platformIdList = self.strategy_conditions.get("platformSaleNum").get("platformIdList")
        sales_dict = self.get_platform_sales(sku_list,
                                             "all",
                                             sku_sale_recent_days, platformIdList)
        # 匹配策略
        if all([self.strategy_conditions.get("platformSaleNum").get("numMin"),
                self.strategy_conditions.get("platformSaleNum").get("numMax")]):
            sales_dict = set(filter(
                lambda x: int(self.strategy_conditions.get("platformSaleNum").get("numMin")) <= x[1] <= int(
                    self.strategy_conditions.get("platformSaleNum").get("numMax")),
                sales_dict.items()))

        elif all([self.strategy_conditions.get("platformSaleNum").get("numMin"),
                  not self.strategy_conditions.get("platformSaleNum").get("numMax")]):
            sales_dict = set(
                filter(lambda x: int(self.strategy_conditions.get("platformSaleNum").get("numMin")) <= x[1],
                       sales_dict.items()))

        elif all([not self.strategy_conditions.get("platformSaleNum").get("numMin"),
                  self.strategy_conditions.get("platformSaleNum").get("numMax")]):
            sales_dict = set(filter(lambda x: x[1] <= int(self.strategy_conditions.get("platformSaleNum").get("numMax")), sales_dict.items()))

        # 得到符合策略的sku列表
        sku_list = list(map(lambda x: x[0], sales_dict))
        # 得到过滤后的集合
        data_set = set(filter(lambda x: x[5] in sku_list, oringin_data_set))
        INFO.logger.info(f"平台listing销量1234过滤了{set(map(lambda x: x[5], oringin_data_set - data_set))}")
        return data_set

    def get_platform_sales(self, sku_list:list[str], orderSourceType, sku_sales_days,platformIdList=None):
        """
        得到各个平台销量
        """
        sku_list = list(set(sku_list))
        url = """https://api-dw.zhcxkj.com/dw/erp/salesvolume/platform/salessku"""
        if orderSourceType != "all":
            param = {
                "input": {
                    "customerId": 1,
                    "orderSourceType": orderSourceType,
                    "skus": sku_list,
                    "days": sku_sales_days
                }
            }
        else:
            param = {
                "input": {
                    "customerId": 1,
                    "orderSourceType": None,
                    "skus": sku_list,
                    "days": sku_sales_days
                }
            }
        hearders = {
            "Content-Type": "application/json",
            "Authorization": "Basic aXJvYm90Ym94OmkxcjVvOEJvdGJveA==",
        }

        res = requests.post(url=url, headers=hearders, json=param)
        sales = {}
        for sku in sku_list:
            sales[sku] = 0
        if isinstance(orderSourceType, int):
            for data in res.json().get('data'):
                if data.get('orderSourceType') == orderSourceType:
                    if data["sku"] not in sales:
                        sales[data["sku"]] = data["salesVolume"]
                    else:
                        sales[data["sku"]] += data["salesVolume"]
            return sales
        else:
            for data in res.json().get('data'):
                if platformIdList:
                    if data['orderSourceType'] in platformIdList:
                        sales[data["sku"]] += data["salesVolume"]
                else:
                    sales[data["sku"]] += data["salesVolume"]
            return sales

    def filter_publish_num(self, data_set):
        origin_data_set = data_set
        # sku_set = set(map(lambda x: x[5], data_set))


        get_publish_num = """
        SELECT IFNULL(count(distinct source_sku),0) publish_num
        FROM test_zhcxkj_center_listing_comprehensive.t_walmart_listing
        WHERE sku = %s
        """
        publish_num = self.db.fetch_all_tuple(get_publish_num, (data_set[5],))
        if all([self.strategy_conditions.get('publishNum').get('numMin'),self.strategy_conditions.get('publishNum').get('numMax')]):
            publish_num_max = int(self.strategy_conditions.get('publishNum').get('numMax'))
            publish_num_min = int(self.strategy_conditions.get('publishNum').get('numMin'))
            if publish_num_min <= publish_num[0][0] <= publish_num_max:
                return True
            else:
                # ERROR.logger.error(f"上架次数过滤了data_set={data_set}")
                return False
            # source_sku_set = set(filter(lambda x: publish_num_min <= x[1] <= publish_num_max, publish_num))
            # source_sku_set = set(map(lambda x:x[0],source_sku_set))

        elif all([not self.strategy_conditions.get('publishNum').get('numMin'),self.strategy_conditions.get('publishNum').get('numMax')]):
            publish_num_max = int(self.strategy_conditions.get('publishNum').get('numMax'))
            if publish_num[0][0] <= publish_num_max:
                return True
            else:
                # ERROR.logger.error(f"上架次数过滤了data_set={data_set}")
                return False
        elif all([self.strategy_conditions.get('publishNum').get('numMin'),not self.strategy_conditions.get('publishNum').get('numMax')]):
            publish_num_min = int(self.strategy_conditions.get('publishNum').get('numMin'))
            if publish_num_min <= publish_num[0][0]:
                return True
            else:
                # ERROR.logger.error(f"上架次数过滤了data_set={data_set}")
                return False
            # if source_sku_set:
            #     get_data_list = """
            #             SELECT id,source_id,source_sku,first_publish_time,sale_price,sku
            #     FROM `test_zhcxkj_center_listing_comprehensive`.`t_walmart_listing`
            #     WHERE  source_id in %s
            #     AND is_deleted=0
            #     """
            #     data_list = self.db.fetch_all_tuple(get_data_list, (source_sku_set,))
            #     data_set = set(data_list)
            #     return data_set
            # return set()


if __name__ == '__main__':
    SkuFilter(2).sku_filter(15618)
